﻿using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Yz.Core.Tools;
using Z.EntityFramework.Plus;

namespace Yz.Core.DBUtility
{
    /// <summary>
    ///     EF扩展方法
    /// </summary>
    public static class EFSqlServerExtension
    {
        #region 数据批量处理

        /// <summary>
        ///     批量删除数据
        /// </summary>
        /// <typeparam name="TEntity">需求更新的实体</typeparam>
        /// <param name="source">数据源</param>
        /// <returns></returns>
        public static int BulkDeleteSqlServer<TEntity>(this IQueryable<TEntity> source)
            where TEntity : class
        {
            return source.Delete();
        }

        /// <summary>
        ///     批量插入数据
        /// </summary>
        /// <typeparam name="T">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        public static int BulkInsertSqlServer<T>(this IList<T> list, DbContext dbContext, bool hasKey = false)
        {
            var connString = dbContext.Database.GetDbConnection().ConnectionString;
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable(hasKey);

            var entityMap = EFExtension.GetEntityMap(typeof(T), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            #region SqlBulkCopy

            var sqlConn = new SqlConnection(connString);
            var bulkCopy = new SqlBulkCopy(sqlConn);
            if (hasKey)
            {
                bulkCopy = new SqlBulkCopy(connString, SqlBulkCopyOptions.KeepIdentity);
            }
            bulkCopy.DestinationTableName = entityMap.TableName;
            bulkCopy.BatchSize = sucCount;
            try
            {
                sqlConn.Open();
                foreach (var pMap in entityMap.PropertyMaps)
                {
                    var propertyName = pMap.PropertyName;
                    var isKey = entityMap.KeyMaps.FirstOrDefault(k => k.PropertyName == propertyName);
                    if (isKey != null && !hasKey)
                    {
                        continue;
                    }
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();
                    bulkCopy.ColumnMappings.Add(columnName, propertyName);
                }

                bulkCopy.WriteToServer(sourcedt);
                return sucCount;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量新增");
                return -1;
            }
            finally
            {
                sqlConn.Close();
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }
            }

            #endregion SqlBulkCopy

        }
        /// <summary>
        ///     批量插入数据
        /// </summary>
        /// <typeparam name="T">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        public static async Task<int> BulkInsertSqlServerAsync<T>(this IList<T> list, DbContext dbContext, bool hasKey = false)
        {
            var connString = dbContext.Database.GetDbConnection().ConnectionString;
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable(hasKey);

            var entityMap = EFExtension.GetEntityMap(typeof(T), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            #region SqlBulkCopy

            var sqlConn = new SqlConnection(connString);
            var bulkCopy = new SqlBulkCopy(sqlConn);
            if (hasKey)
            {
                bulkCopy = new SqlBulkCopy(connString, SqlBulkCopyOptions.KeepIdentity);
            }
            bulkCopy.DestinationTableName = entityMap.TableName;
            bulkCopy.BatchSize = sucCount;
            try
            {
                sqlConn.Open();
                foreach (var pMap in entityMap.PropertyMaps)
                {
                    var propertyName = pMap.PropertyName;
                    var isKey = entityMap.KeyMaps.FirstOrDefault(k => k.PropertyName == propertyName);
                    if (isKey != null && !hasKey)
                    {
                        continue;
                    }
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();
                    bulkCopy.ColumnMappings.Add(columnName, propertyName);
                }

                await bulkCopy.WriteToServerAsync(sourcedt);
                return sucCount;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量新增");
                return -1;
            }
            finally
            {
                sqlConn.Close();
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }
            }

            #endregion SqlBulkCopy

        }
        /// <summary>
        ///     批量修改数据
        /// </summary>
        /// <typeparam name="TEntity">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        /// <param name="updateExpression">更新的字段信息</param>
        /// <returns></returns>
        public static int BulkUpdateSqlServer<TEntity>(this IList<TEntity> list, DbContext dbContext,
            Expression<Func<TEntity, TEntity>> updateExpression)
            where TEntity : class
        {
            var connString = dbContext.Database.GetDbConnection().ConnectionString;
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable();
            if (updateExpression == null)
            {
                throw new ArgumentNullException("updateExpression");
            }
            var entityMap = EFExtension.GetEntityMap(typeof(TEntity), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            var conn = new SqlConnection(connString);
            var comm = conn.CreateCommand();
            comm.CommandTimeout = 30000;
            comm.CommandType = CommandType.Text;
            conn.Open();
            var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            var sql = "select * from " + entityMap.TableName;
            var selcom = new SqlCommand(sql, conn, tran);
            var adapter = new SqlDataAdapter(selcom);
            var commandBulider = new SqlCommandBuilder(adapter)
            {
                ConflictOption = ConflictOption.OverwriteChanges
            };
            try
            {
                var suc = 0;
                var Upcommand = new SqlCommand();

                #region 生成相关语句

                var sqlBuilder = new StringBuilder();
                sqlBuilder.Append("UPDATE ");
                sqlBuilder.Append(entityMap.TableName);
                sqlBuilder.AppendLine(" SET ");
                if (updateExpression.Body as MemberInitExpression == null)
                {
                    throw new ArgumentException("The update expression must be of type MemberInitExpression.",
                        "updateExpression");
                }

                var wroteSet = false;
                foreach (var binding in (updateExpression.Body as MemberInitExpression).Bindings)
                {
                    if (wroteSet)
                    {
                        sqlBuilder.AppendLine(", ");
                    }
                    var propertyName = binding.Member.Name;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();

                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", columnName);
                    wroteSet = true;
                }

                sqlBuilder.AppendLine();
                sqlBuilder.Append(" where ");

                var wroteKey = false;
                foreach (var keyMap in entityMap.KeyMaps)
                {
                    if (wroteKey)
                    {
                        sqlBuilder.Append(" AND ");
                    }
                    var propertyName = keyMap.PropertyName;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();
                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", keyMap.ColumnName);
                    wroteKey = true;
                }

                Upcommand.CommandText = sqlBuilder.ToString();

                #endregion 生成相关语句

                adapter.UpdateBatchSize = list.Count;
                sourcedt.AcceptChanges();
                Upcommand.Connection = conn;
                Upcommand.Transaction = tran;
                Upcommand.UpdatedRowSource = UpdateRowSource.None;
                foreach (DataRow dr in sourcedt.Rows)
                {   //所有行设为修改状态
                    dr.SetModified();
                }
                adapter.UpdateCommand = Upcommand;
                suc = adapter.Update(sourcedt);
                sourcedt.AcceptChanges();

                tran.Commit();
                return suc;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量修改");
                tran.Rollback();
                return -1;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        ///     批量修改数据(根据特定条件列)
        /// </summary>
        /// <typeparam name="TEntity">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        /// <param name="updateExpression">更新的字段信息</param>
        /// <param name="whereColumns">特定条件列</param>
        /// <returns></returns>
        public static int BulkUpdateByColumnSqlServer<TEntity>(this IList<TEntity> list, DbContext dbContext,
            Expression<Func<TEntity, object>> updateExpression, Expression<Func<TEntity, object>> whereColumns)
            where TEntity : class
        {
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable();
            if (updateExpression == null)
            {
                throw new ArgumentNullException("updateExpression");
            }
            var entityMap = EFExtension.GetEntityMap(typeof(TEntity), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            var conn = new SqlConnection(dbContext.Database.GetDbConnection().ConnectionString);
            var comm = conn.CreateCommand();
            comm.CommandTimeout = 30000;
            comm.CommandType = CommandType.Text;
            conn.Open();
            var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            var sql = "select * from " + entityMap.TableName;
            var selcom = new SqlCommand(sql, conn, tran);
            var adapter = new SqlDataAdapter(selcom);
            var commandBulider = new SqlCommandBuilder(adapter)
            {
                ConflictOption = ConflictOption.OverwriteChanges
            };
            try
            {
                var suc = 0;
                var Upcommand = new SqlCommand();

                #region 生成相关语句

                var sqlBuilder = new StringBuilder();
                sqlBuilder.Append("UPDATE ");
                sqlBuilder.Append(entityMap.TableName);
                sqlBuilder.AppendLine(" SET ");
                if (updateExpression.Body as MemberInitExpression == null)
                {
                    throw new ArgumentException("The update expression must be of type MemberInitExpression.",
                        "updateExpression");
                }

                ///拼装修改数据列
                var wroteSet = false;
                foreach (var binding in (updateExpression.Body as MemberInitExpression).Bindings)
                {
                    if (wroteSet)
                    {
                        sqlBuilder.AppendLine(", ");
                    }
                    var propertyName = binding.Member.Name;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();

                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", columnName);
                    wroteSet = true;
                }

                sqlBuilder.AppendLine();
                ///拼装Where条件
                sqlBuilder.Append(" where ");
                if (updateExpression.Body as MemberInitExpression == null)
                {
                    throw new ArgumentException("The update expression must be of type MemberInitExpression.",
                        "whereColumns");
                }

                var columnSet = false;
                foreach (var binding in (updateExpression.Body as MemberInitExpression).Bindings)
                {
                    if (columnSet)
                    {
                        sqlBuilder.AppendLine(", ");
                    }
                    var propertyName = binding.Member.Name;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();

                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", columnName);
                    columnSet = true;
                }

                Upcommand.CommandText = sqlBuilder.ToString();

                #endregion 生成相关语句

                adapter.UpdateBatchSize = list.Count;
                sourcedt.AcceptChanges();
                Upcommand.Connection = conn;
                Upcommand.Transaction = tran;
                Upcommand.UpdatedRowSource = UpdateRowSource.None;
                foreach (DataRow dr in sourcedt.Rows)
                {    //所有行设为修改状态
                    dr.SetModified();
                }
                adapter.UpdateCommand = Upcommand;
                suc = adapter.Update(sourcedt);
                sourcedt.AcceptChanges();

                tran.Commit();
                return suc;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量修改");
                tran.Rollback();
                return -1;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        #endregion 数据批量处理

    }
}
